import json
import sys
import time
import datetime
from cacheout import Cache

sys.path.extend(["F:/ML/BNC", "D:/BNC", "/www/wwwroot/BNC"])
from WebServer.blog.DB.DBValueConstants import ENABLE
from WebServer.DataBaseEnum import NOT_DISPOSE
from Utils.Funcc import in_sql

cache = Cache(maxsize=256, ttl=60 * 6, timer=time.time, default=None)
from .DB.DBConnection import DB_POOL
from DataBase.DatabaseConsts import MarketTemperatureAutoTableName, MarketProfitAutoTableName, \
    CodeTagDateWeightTempTableName, TuShareStockBasicTableName, MarketHighLimitAutoTableName, \
    MarketInfoDB, CodeTagDateTableName, AStockTagDB, StockTagRenameTableName, StockTagManagerDB

DB_DATE_FORMAT = "%Y%m%d"
DB_DATE_FORMAT_SPILT_BY_ = "%Y-%m-%d"
strptime, strftime = datetime.datetime.strptime, datetime.datetime.strftime


class MarkStatisticMapper():
    def __init__(self):
        from DataBase.DatabaseConsts import MarketInfoDB
        self.DB_pool = DB_POOL.get_remote_linux_db_pool(db=MarketInfoDB, )

    def get_market_temperature(self, start_date, end_date, ):
        # where        MTA.date >= % s and MTA.date <= % s
        connection_ = self.DB_pool.connection()
        cursor_ = connection_.cursor()

        try:
            def in_sql(key_words):
                base_li = ['']
                base_li.extend(key_words)
                return str(base_li).replace("[", '(').replace("]", ')')

            select_temper_sql = """SELECT MTA.date AS date,MTA.gaobiao_count AS gaobiao_count,MTA.low_limit_with_volume_count AS low_limit_with_volume_count,MTA.reach_high_limit_count AS reach_high_limit_count,MTA.high_limit_count AS high_limit_count,MTA.reach_minus5_count AS reach_minus5_count,MTA.high_reach_high_ratio AS high_reach_high_ratio,MPA.highlimit_close_profit_ratio AS highlimit_close_profit_ratio,MPA.highlimit_crack_close_profit_ratio AS  highlimit_crack_close_profit_ratio FROM {} MTA INNER JOIN {} MPA ON MPA.date = MTA.date WHERE 	MTA.reach_high_limit_count != 0 and MTA.date >= %s and MTA.date <= %s  ORDER BY MTA.date""".format(
                MarketTemperatureAutoTableName, MarketProfitAutoTableName)
            cursor_.execute(select_temper_sql,
                            [strftime(start_date, DB_DATE_FORMAT), strftime(end_date, DB_DATE_FORMAT)])
            # 选取当前最大的两个
            _temp_res = cursor_.fetchall()
            _new_res = list()  # [{date:'','gaobiao_count' :'' , }]

            # 修改数据的格式 : 小数点
            for one_res_ in _temp_res:
                one_res_['highlimit_close_profit_ratio'] = round(float(one_res_['highlimit_close_profit_ratio']), 2)
                one_res_['highlimit_crack_close_profit_ratio'] = round(
                    float(one_res_['highlimit_crack_close_profit_ratio']), 2)
                one_res_['high_reach_high_ratio'] = round(float(one_res_['high_reach_high_ratio']), 1)

            dates_set = set()

            for one_res_ in _temp_res:
                dates_set.add(one_res_['date'])
            # select_relate_code_sql = """select MHA.* from {} AS MHA INNER JOIN (SELECT date, n, CODE, limit_count FROM ( SELECT @prev := '' ,@prev_limit_count := - 1000, @n := 0 ) init JOIN ( SELECT @n := IF ( MHA.date != @prev, 1, IF ( MHA.limit_count = @prev_limit_count ,@n, @n + 1 ) ) AS n, @prev := MHA.date, @prev_limit_count := MHA.limit_count, MHA.date, MHA. CODE, MHA.limit_count FROM {} AS MHA WHERE MHA.hold_on = 1 AND MHA.stock_type = 0 AND MHA.limit_count >= 1 AND MHA.date IN {} ORDER BY MHA.date, MHA.limit_count DESC ) x where n <= 2 ORDER BY date , n) AS X ON X.date = MHA.date and X.code = MHA.code;""".format(

            select_relate_code_sql = """select MHA.* from {} AS MHA INNER JOIN (SELECT date, n, CODE, limit_count FROM ( SELECT @prev := '' ,@prev_limit_count := - 1000, @n := 0 ) init JOIN ( SELECT @n := IF ( MHA.date != @prev, 1, IF ( MHA.limit_count = @prev_limit_count ,@n, @n + 1 ) ) AS n, @prev := MHA.date, @prev_limit_count := MHA.limit_count, MHA.date, MHA. CODE, MHA.limit_count FROM {} AS MHA WHERE  MHA.stock_type = 0 AND MHA.hold_on = 1 AND MHA.date IN {} ORDER BY MHA.date, MHA.limit_count DESC ) x where n <= 2 ORDER BY date , n) AS X ON X.date = MHA.date and X.code = MHA.code;""".format(
                MarketHighLimitAutoTableName,
                MarketHighLimitAutoTableName,
                in_sql(dates_set))
            cursor_.execute(select_relate_code_sql)
            res = cursor_.fetchall()
            # 按日期进行处理
            date_relate_code = dict()  # {'date' : [{'code':'','name':''}] , }
            for one_res_ in res:
                _date = one_res_['date']
                date_relate_code.setdefault(_date, list())
                date_relate_code[_date].append({'code':one_res_['code'],"date":one_res_['date'],"name":one_res_['name'],"limit_count":one_res_['limit_count']})

            for one_res_ in _temp_res:
                _date = int(one_res_['date'])
                if _date in date_relate_code:
                    one_res_['high_level_code'] = date_relate_code[_date]
                else:
                    one_res_['high_level_code'] = list()

            return _temp_res
        finally:
            cursor_.close()
            connection_.close()

    # 需要提取出全部的天数
    # 保存当日最大的两个数据
    def get_high_reach_high(self, start_date, end_date, ):
        # where        MTA.date >= % s and MTA.date <= % s
        connection_ = self.DB_pool.connection()
        cursor_ = connection_.cursor()
        try:
            select_temper_sql = """select * from (SELECT MTA.date AS date, MTA.continue_high_ratio, MTA.first_high_count, MTA.second_high_count, MTA.third_high_count, MTA.forth_high_count, (  MTA.fifth_high_count + MTA.sixth_high_count ) AS fifth_high_count FROM {} AS MTA where date >= %s and date <= %s ORDER BY date DESC ) A  ORDER BY date """.format(
                MarketTemperatureAutoTableName)
            cursor_.execute(select_temper_sql,
                            [strftime(start_date, DB_DATE_FORMAT), strftime(end_date, DB_DATE_FORMAT)])
            # , [start_date, end_date,]
            return cursor_.fetchall()
        finally:
            cursor_.close()
            connection_.close()


class StockCalendarMapper():
    def __init__(self):
        self.DB_pool = DB_POOL.get_local_db_pool(db=StockTagManagerDB)

    @cache.memoize(ttl=60 * 3)
    def __get_dic_from_file(self):
        fp = "F:/ML/BNC/WebServer/static/StockCalendar.json"
        with open(fp, 'r', encoding="UTF8") as f:
            return json.load(f)
            # jso_s = f.read()
        # return json.load(fp)
        # return demjson.decode(jso_s)

    def select_stock_calendar(self, start_date, end_date):
        di = self.__get_dic_from_file()
        di2 = di.copy()
        # fixme 消除掉
        # di2['data'] = list(
        #     filter(lambda x: end_date >= datetime.datetime.strptime(str(x['date']), "%Y%m%d").date() >= start_date,
        #            di2['data']))
        return di2

    def select_stock_calendar_from_db(self, start_date: datetime.date or None, end_date: datetime.date or None,
                                      one_day_tags_size: int = 5, tag_fre_low_line=5):
        DAY_MIN_TAG_FRE_ = 5
        format_di = {"st_db": StockTagManagerDB, 'ctd_tb': CodeTagDateTableName, "enb": ENABLE}
        # warning 日期使用的是 DB_DATE_FROMATE_SPILT_BY_
        select_sql = "SELECT date,tag,fre as incr FROM (SELECT @prev := '' ,@n := 0) init JOIN ( SELECT @n := IF (SC.date != @prev, 1 ,@n + 1) AS n, @prev := SC.date, SC.date, tag, fre FROM ( SELECT date, tag,count(*) AS fre FROM ( SELECT distinct date,code, IFNULL(TR.`to`, CTD.tag) AS tag FROM {st_db}.{ctd_tb} AS CTD LEFT OUTER JOIN stock_tag_manager.tag_rename AS TR ON TR.origin = CTD.tag WHERE TR.enable = {enb} AND CTD.date >= %s AND CTD.date <= %s ) AS A GROUP BY date, tag ) AS SC HAVING SC.fre >= %s ORDER BY SC.date ASC, SC.fre DESC ) x WHERE x.n <= %s".format(
            **format_di)
        connection_ = self.DB_pool.connection()
        cursor_ = connection_.cursor()
        try:
            cursor_.execute(select_sql,
                            [start_date.strftime(DB_DATE_FORMAT_SPILT_BY_), end_date.strftime(DB_DATE_FORMAT_SPILT_BY_),
                             DAY_MIN_TAG_FRE_, one_day_tags_size])
            return cursor_.fetchall()
        finally:
            cursor_.close()
            connection_.close()


class TagRelaMapper():
    def __init__(self):
        self.DB_pool = DB_POOL.get_local_db_pool(db=StockTagManagerDB, )

    def select_rela_codes(self, tag, num):
        connection_ = self.DB_pool.connection()
        cursor_ = connection_.cursor()
        try:
            select_rela_codes_sql = "SELECT CTDWT.CODE AS code, IFNULL(SB. NAME, '默认股票名') AS name, cur_up_times AS cur_up_times, cur_date AS cur_date, cur_count AS cur_count, ( cur_up_times / IF (cur_count = 0 ,- 1, cur_count) ) AS rela_ratio FROM code_tag_date_weight_temp AS CTDWT INNER JOIN ( select CODE,max(cur_date) AS last_date,code_tag_date_weight_temp.tag from code_tag_date_weight_temp GROUP BY code,tag ) AS MX_CTDWT ON MX_CTDWT.code = CTDWT.code AND MX_CTDWT.last_date = CTDWT.cur_date AND CTDWT.tag = MX_CTDWT.tag INNER JOIN stockbasic AS SB ON SB.symbol = CTDWT.CODE WHERE CTDWT.tag = %s ORDER BY cur_up_times DESC, rela_ratio DESC limit %s;"
            cursor_.execute(select_rela_codes_sql, [tag, num])
            return cursor_.fetchall()
        finally:
            cursor_.close()
            connection_.close()

    def select_rela_tag(self, code_):
        connection_ = self.DB_pool.connection()
        cursor_ = connection_.cursor()
        try:
            select_rela_tags_sql = "SELECT TS.tag, CTDWT.code AS code, IFNULL(SB. NAME, '默认股票名') AS name, cur_up_times AS cur_up_times, cur_date AS cur_date, cur_count AS cur_count, ( cur_up_times / IF (cur_count = 0 ,- 1, cur_count) ) AS rela_ratio FROM code_tag_date_weight_temp AS CTDWT INNER JOIN ( select CODE,max(cur_date) AS last_date,tag from code_tag_date_weight_temp GROUP BY code,tag ) AS MX_CTDWT ON MX_CTDWT.code = CTDWT.code AND MX_CTDWT.last_date = CTDWT.cur_date AND MX_CTDWT.tag = CTDWT.tag INNER JOIN stockbasic AS SB ON SB.symbol = CTDWT.code INNER JOIN code_tag_relation AS CTR ON CTR.code = CTDWT.code AND CTR.tag = CTDWT.tag AND CTR. ENABLE = 1 INNER JOIN tag_rename AS TR ON CTR.tag = TR.origin AND TR. ENABLE = 1 INNER JOIN tag_status AS TS ON TS.tag = TR.`to` AND TS. ENABLE = 1 WHERE CTDWT.CODE = %s ORDER BY cur_up_times DESC, rela_ratio DESC"
            cursor_.execute(select_rela_tags_sql, [code_])
            return cursor_.fetchall()
        finally:
            cursor_.close()
            connection_.close()

    def select_rela_codes_by_dtp(self, date, tag, plate):
        connection_ = self.DB_pool.connection()
        cursor_ = connection_.cursor()
        try:
            return {'data': [
                {"code": "300317", "name": "假的", "cur_up_times": "1", "cur_date": "20191014", "cur_count": "2",
                 "rela_ratio": "0.1"},
                {"code": "300317", "name": "假的", "cur_up_times": "1", "cur_date": "20191014", "cur_count": "2",
                 "rela_ratio": "0.1"},
                {"code": "300317", "name": "假的", "cur_up_times": "1", "cur_date": "20191014", "cur_count": "2",
                 "rela_ratio": "0.1"}]}
            # return cursor_.fetchall()
        finally:
            cursor_.close()
            connection_.close()

    def select_continue_stock_infos(self, cur_date: datetime, cur_tag):
        connection_ = self.DB_pool.connection()
        cursor_ = connection_.cursor()
        skip_tag_set = {"新股", "低价", "次新", "超跌", "次新股", "股权转让", "高送转", "并购", "填权", "公告", "庄股", "18年报股", "上市新股", "业绩预增", "复牌",
                "业绩", "妖股", "新股上市", "老妖", "股权", "涨价", "高送转预期", "定增", "回购", "超跌反弹", "业绩增长", "举牌", "17年报高送转填权", "首批",
                "价值重估"}
        format_di = {"st_db": StockTagManagerDB, "tr_tb": StockTagRenameTableName, "CTDWT": "code_tag_date_weight_temp",
                     "ms_db": MarketInfoDB, "mha_tb": MarketHighLimitAutoTableName,
                     "sts": in_sql(skip_tag_set), "enb": ENABLE,
                     "stm_db" : StockTagManagerDB,
                     }
        # select_sql = 'SELECT * FROM {ms_db}.{mha_tb} AS MHA INNER JOIN ( SELECT CTDWT.* FROM ( SELECT DISTINCT CODE, date, IFNULL(TR.`TO`, CTD.tag) AS tag FROM {st_db}.code_tag_date AS CTD LEFT OUTER JOIN {st_db}.{tr_tb} AS TR ON CTD.tag = TR.`from` WHERE TR.dispose = {ndp} AND  IFNULL(TR.`TO`, CTD.tag) = %s AND date = %s ) AS A INNER JOIN ( SELECT CODE, tag, cur_up_times, cur_date, ( cur_up_times / ( IF (cur_count = 0, - 1, cur_count) ) ) AS contribution, cal_date,cur_count FROM {st_db}.{CTDWT} AS CTDWT ) AS CTDWT ON CTDWT. CODE = A. CODE AND CTDWT.cal_date = %s AND CTDWT.tag = A.tag AND A.tag not IN {sts} ORDER BY CTDWT.contribution DESC ) AS CTW ON CTW. CODE = MHA. CODE AND CTW.cal_date = MHA.date WHERE limit_count >= 1 AND hold_on = 1 AND stock_type = 0 ORDER BY contribution DESC'.format(

        select_sql = 'SELECT * FROM {ms_db}.{mha_tb} AS MHA INNER JOIN ( SELECT CTDWT.* FROM ( SELECT DISTINCT CODE, date, IFNULL(TR.origin, CTD.tag) AS tag FROM {st_db}.code_tag_date AS CTD LEFT OUTER JOIN {stm_db}.{tr_tb} AS TR ON CTD.tag = TR.origin WHERE TR.enable = {enb} AND  IFNULL(TR.origin, CTD.tag) = %s AND date = %s ) AS A INNER JOIN ( SELECT CODE, tag, cur_up_times, cur_date, ( cur_up_times / ( IF (cur_count = 0, - 1, cur_count) ) ) AS contribution, cal_date,cur_count FROM {stm_db}.{CTDWT} AS CTDWT ) AS CTDWT ON CTDWT. CODE = A. CODE AND CTDWT.cal_date = %s AND CTDWT.tag = A.tag AND A.tag not IN {sts} ORDER BY CTDWT.contribution DESC ) AS CTW ON CTW. CODE = MHA. CODE AND CTW.cal_date = MHA.date WHERE limit_count >= 1  AND stock_type = 0 ORDER BY contribution DESC'.format(
            **format_di)
        try:
            cursor_.execute(select_sql, [cur_tag, cur_date.strftime("%Y-%m-%d"), cur_date.strftime(DB_DATE_FORMAT)])
            return cursor_.fetchall()
        finally:
            cursor_.close()
            connection_.close()
